﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using BCCommon.Extensions;
using BCCommon.OAEnums;
using BCEntity.OA.Attendance.AttendanceGroup;
using BCEntity.OA.Attendance.AttendanceLocation;
using BCEntity.OA.Attendance.AttendancePlan;
using BCEntity.OA.Attendance.AttendanceWiFi;
using BCCommon;

namespace BCData.OA.Attendance.AttendanceGroup
{
    public class AttendanceGroupData : IAttendanceGroupData
    {
        private const string QUERY = @"SELECT GroupId,IsDefault,GroupName,Type,MemberCount,CompanyId,DepartmentIds,ManagerIds,StartTime,WorkDate,IsAllowOuterCheck,
                                            IsOuterRemark,LaterMinutes,RemedyRuleId,ClassId,IgnoreMemberIds,ModeIds,PlanClassIds,PlanName,CycleDays,IsAutoCheck,RemedyRuleId,EffectiveDate,
                                            AdminId,AdminName,CreateTime,ModifierId,ModifierName,ModifyTime,Status
                                        FROM OA_AttendanceGroup 
                                        WHERE Status<>'Abandon'";

        private readonly IDatabaseContext databaseContext;

        public AttendanceGroupData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        /// <summary>
        /// 校验考勤组息存在不
        /// </summary>
        /// <param name="groupId">考勤组Id</param>
        /// <returns></returns>
        public async Task<bool> CheckIsExist(long groupId)
        {
            string sql = @"SELECT 1 FROM OA_AttendanceGroup WHERE GroupId=?GroupId AND  Status<>'" + AttendanceBaseStatus.Abandon.ToString() + "' LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?GroupId", groupId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameter) != null;
        }

        /// <summary>
        /// 校验部门考勤组息存在不
        /// </summary>
        /// <param name="departmentId">部门Id</param>
        /// <param name="companyId">公司Id</param>
        /// <returns></returns>
        public async Task<bool> CheckDepartmentIsExist(int departmentId, int companyId)
        {
            string sql = @"SELECT 1 FROM OA_AttendanceGroup WHERE FIND_IN_SET(?DepartmentId,DepartmentIds) AND CompanyId=?CompanyId AND  Status<>'" + AttendanceBaseStatus.Abandon.ToString() + "' LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        #region 删除操作
        /// <summary>
        /// 删除考勤组
        /// </summary>
        /// <param name="groupId">考勤组Id</param>
        /// <param name="adminName">管理员信息</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long groupId, string adminName = null)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceGroup", new string[] { "Status", "ModifierName", "ModifyTime" }, new string[] { "GroupId" });
            parameters.Add("?GroupId", groupId, DbType.Int64);
            parameters.Add("?Status", "Abandon", DbType.AnsiString, size: 50);
            parameters.Add("?ModifierName", adminName, DbType.AnsiString, size: 50);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters,databaseContext.Transaction);
        }

        #endregion

        #region 查询操作


        /// <summary>
        /// 分页获取考勤组信息列表
        /// </summary>
        /// <param name="pageIndex">起始页</param>
        /// <param name="pageSize">页数量</param>
        /// <param name="ascending">排序</param>
        /// <returns></returns>
        public async Task<(IEnumerable<AttendanceGroupEntity>, int)> GetAsync(long adminId, int pageIndex, int pageSize, bool ascending, string groupName, AttendanceType? type, CheckInMode? checkInMode)
        {
            string sql = QUERY;

            DynamicParameters parameters = new DynamicParameters();
            var wheres = new List<string>();
            if (adminId > 0)
            {
                wheres.Add(" FIND_IN_SET(ManagerIds,?AdminId) ");
                parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            }
            if (groupName.IsNotNull())
            {
                wheres.Add(" GroupName=?GroupName");
                parameters.Add("?GroupName", groupName.Trim(), DbType.AnsiString, size: 50);
            }
            if (type.HasValue)
            {
                wheres.Add(" Type=?Type");
                parameters.Add("?Type", type.Value.ToString(), DbType.AnsiString);
            }
            if (checkInMode.HasValue)
            {
                wheres.Add(" FIND_IN_SET(ModeIds,?CheckInMode)");
                parameters.Add("?CheckInMode", checkInMode.ToString(), DbType.AnsiString);
            }
            parameters.Add("?pageIndex", (pageIndex - 1) * pageSize, DbType.Int32);
            parameters.Add("?pageSize", pageSize, DbType.Int32);

            sql = string.Format("{0} {2} ORDER BY Type {1} LIMIT ?pageIndex,?pageSize; SELECT COUNT(1) FROM OA_AttendanceGroup  Where Status<>'Abandon' {2};",
                sql,
                ascending ? "ASC" : "DESC",
                wheres.Count > 0 ? string.Format(" ", string.Join(" AND ", wheres)) : string.Empty);

            return await this.databaseContext.Slave.QueryMultipleAsync<AttendanceGroupEntity,int>(sql, parameters);
        }

        /// <summary>
        /// 获取考勤组信息
        /// </summary>
        /// <param name="groupId">班次Id</param>
        /// <returns></returns>
        public async Task<AttendanceGroupEntity> GetEntityAsync(long groupId)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceGroupEntity>(string.Format("{0} AND GroupId=?GroupId", QUERY), parameters);
        }

        public async Task<AttendanceGroupEntity> GetFirstNoneAttendanceGroup()
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Type", "NONE", DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceGroupEntity>(string.Format("{0} AND Type=?Type", QUERY), parameters);
        }

        /// <summary>
        /// 根据考勤组Id获取固定设置信息列表
        /// </summary>
        /// <param name="groupId">考勤组Id</param>
        /// <returns></returns>
        public async Task<IEnumerable<AttendanceGroupFixedViewEntity>> GetGroupFixedByGroupId(long groupId)
        {
            string sql = "select A.*,B.WeekDay as WorkDay,B.ClassName from  OA_AttendanceGroupFixed A " +
                          "LEFT JOIN oa_attendanceclass B on A.ClassId = B.ClassId   Where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<AttendanceGroupFixedViewEntity>(string.Format("{0} AND GroupId=?GroupId", sql), parameters);
        }

        #endregion

        #region 新增操作
        public async Task<AttendanceGroupEntity> InsertAsync(AttendanceGroupEntity entity, List<AttendanceLocationEntity> locationEntities, List<AttendanceWiFiEntity> wiFiEntities, List<AttendanceGroupFixedEntity> fixedEntities = null, List<AttendancePlanEntity> planEntities = null)
        {
            long gropuId = this.GetInsertGroupSql(entity);
            List<DynamicParameters> parameterList = new List<DynamicParameters>();
            List<string> sqls = new List<string>();

            if (entity.Type == AttendanceType.FIXED.ToString())
            {
                foreach (var item in fixedEntities)
                {
                    item.GroupId = gropuId;
                    sqls.Add(GetInsertGroupFixedSql(item, out DynamicParameters parameter));
                    parameterList.Add(parameter);
                }
            }
            if (entity.Type == AttendanceType.TURN.ToString())
            {
                foreach (var item in planEntities)
                {
                    item.GroupId = gropuId;
                    sqls.Add(GetInsertGroupPlanSql(item, out DynamicParameters parameter));
                    parameterList.Add(parameter);
                }
            }
            foreach (var item in locationEntities)
            {
                item.GroupId = gropuId;
                item.AdminId = entity.AdminId;
                item.AdminName = entity.AdminName;
                sqls.Add(GetInsertGroupLocationSql(item, out DynamicParameters parameter));
                parameterList.Add(parameter);
            }

            foreach (var item in wiFiEntities)
            {
                item.GroupId = gropuId;
                item.AdminId = entity.AdminId;
                item.AdminName = entity.AdminName;
                sqls.Add(GetInsertGroupWifiSql(item, out DynamicParameters parameter));
                parameterList.Add(parameter);
            }

            var executeCount = await this.databaseContext.Master.TransactionExecuteAsync(sqls, parameterList, databaseContext.Transaction);
            if (executeCount > 0)
            {
                string querySql = string.Format(@"{0} AND GroupId=" + gropuId, QUERY);
                return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceGroupEntity>(querySql);
            }
            return new AttendanceGroupEntity();
        }

        private string GetInsertGroupPlanSql(AttendancePlanEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetInsertSql("OA_AttendancePlan", new string[] { "ClassId", "GroupId", "WeekDay" });
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64);
            parameters.Add("?GroupId", entity.GroupId, DbType.Int64);
            parameters.Add("?WeekDay", entity.WeekDay, DbType.AnsiString, size: 50);
            return sql;
        }

        private string GetInsertGroupWifiSql(AttendanceWiFiEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetInsertSql("OA_AttendanceWiFi", new string[] { "ModeId", "GroupId", "Name", "MAC", "AdminId", "AdminName" });
            parameters.Add("?ModeId", entity.ModeId, DbType.AnsiString);
            parameters.Add("?GroupId", entity.GroupId, DbType.Int64);
            parameters.Add("?Name", entity.Name, DbType.AnsiString, size: 50);
            parameters.Add("?MAC", entity.MAC, DbType.AnsiString, size: 50);
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64);
            parameters.Add("?AdminName", entity.AdminName, DbType.AnsiString, size: 30);

            return sql;
        }

        public string GetInsertGroupLocationSql(AttendanceLocationEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetInsertSql("OA_AttendanceLocation", new string[] { "LocationId", "ModeId", "GroupId", "LocationName", "Address", "Longitude", "Latitude", "EffectiveScope", "AdminId", "AdminName" });
            parameters.Add("?LocationId", entity.LocationId, DbType.AnsiString);
            parameters.Add("?ModeId", entity.ModeId, DbType.AnsiString);
            parameters.Add("?GroupId", entity.GroupId, DbType.Int64);
            parameters.Add("?LocationName", entity.LocationName, DbType.AnsiString, size: 100);
            parameters.Add("?Address", entity.Address, DbType.AnsiString, size: 500);
            parameters.Add("?Longitude", entity.Longitude, DbType.Double);
            parameters.Add("?Latitude", entity.Latitude, DbType.Double);
            parameters.Add("?EffectiveScope", entity.EffectiveScope, DbType.Double);
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64);
            parameters.Add("?AdminName", entity.AdminName, DbType.AnsiString, size: 30);

            return sql;
        }

        private string GetInsertGroupFixedSql(AttendanceGroupFixedEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetInsertSql("OA_AttendanceGroupFixed", new string[] { "ClassId", "GroupId", "WeekDay" });
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64);
            parameters.Add("?GroupId", entity.GroupId, DbType.Int64);
            parameters.Add("?WeekDay", entity.WeekDay, DbType.AnsiString, size: 50);
            return sql;

        }

        private long GetInsertGroupSql(AttendanceGroupEntity entity)
        {
            string sql = "INSERT INTO OA_AttendanceGroup(ClassId,GroupName,Type,IgnoreMemberIds,MemberCount,CompanyId,DepartmentIds,ManagerIds,IsAllowOuterCheck,IsOuterRemark,ModeIds,AdminId,AdminName,IsDefault,LaterMinutes,StartTime,WorkDate,CycleDays,PlanName,PlanClassIds,RemedyRuleId,EffectiveDate)" +
                         "VALUES(?ClassId,?GroupName,?Type,?IgnoreMemberIds,?MemberCount,?CompanyId,?DepartmentIds,?ManagerIds,?IsAllowOuterCheck,?IsOuterRemark,?ModeIds,?AdminId,?AdminName,?IsDefault,?LaterMinutes,?StartTime,?WorkDate,?CycleDays,?PlanName,?PlanClassIds,?RemedyRuleId,?EffectiveDate); SELECT LAST_INSERT_ID();";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64);
            parameters.Add("?GroupName", entity.GroupName, DbType.AnsiString, size: 50);
            parameters.Add("?Type", entity.Type, DbType.AnsiString, size: 50);
            parameters.Add("?IgnoreMemberIds", entity.IgnoreMemberIds, DbType.AnsiString, size: 50);
            parameters.Add("?MemberCount", entity.MemberCount, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int64);
            parameters.Add("?DepartmentIds", entity.DepartmentIds, DbType.AnsiString, size: 50);
            parameters.Add("?ManagerIds", entity.ManagerIds, DbType.AnsiString, size: 50);
            parameters.Add("?IsAllowOuterCheck", true, DbType.Boolean);
            parameters.Add("?IsOuterRemark", entity.IsOuterRemark, DbType.Boolean);
            parameters.Add("?ModeIds", entity.ModeIds, DbType.AnsiString, size: 50);
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64);
            parameters.Add("?AdminName", entity.AdminName, DbType.AnsiString, size: 50);
            parameters.Add("?IsDefault", entity.IsDefault, DbType.Boolean);
            parameters.Add("?LaterMinutes", entity.LaterMinutes, DbType.Int32);
            parameters.Add("?StartTime", entity.StartTime, DbType.AnsiString, size: 50);
            parameters.Add("?WorkDate", entity.WorkDate, DbType.AnsiString, size: 50);
            parameters.Add("?PlanClassIds", entity.PlanClassIds, DbType.AnsiString, size: 50);
            parameters.Add("?PlanName", entity.PlanName, DbType.AnsiString, size: 50);
            parameters.Add("?CycleDays", entity.CycleDays, DbType.Int32);
            parameters.Add("?RemedyRuleId", entity.RemedyRuleId, DbType.Int64);
            parameters.Add("?EffectiveDate", entity.EffectiveDate, DbType.DateTime);
            return this.databaseContext.Master.ExecuteScalarAsync<long>(sql, parameters, databaseContext.Transaction).Result;
        }
        #endregion

        #region 修改操作
        /// <summary>
        /// 修改考勤组信息
        /// </summary>
        /// <param name="entity">考勤组</param>
        /// <returns></returns>
        public async Task<AttendanceGroupEntity> UpdateAsync(AttendanceGroupEntity entity, List<AttendanceLocationEntity> locationEntities, List<AttendanceWiFiEntity> wiFiEntities, List<AttendanceGroupFixedEntity> fixedEntities = null, List<AttendancePlanEntity> planEntities = null)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> parameterList = new List<DynamicParameters>();

            sqls.Add(this.GetUpdateGroupSql(entity, out DynamicParameters parameter1));
            parameterList.Add(parameter1);
            if (entity.Type == AttendanceType.FIXED.ToString())
            {
                sqls.Add(this.GetDeleteFixedGroupSql(entity.GroupId, out DynamicParameters parameter2));
                parameterList.Add(parameter2);

                foreach (var item in fixedEntities)
                {
                    item.GroupId = entity.GroupId;
                    sqls.Add(GetInsertGroupFixedSql(item, out DynamicParameters parameter));
                    parameterList.Add(parameter);
                }
            }
            if (entity.Type == AttendanceType.TURN.ToString())
            {
                sqls.Add(this.GetDeleteTurnGroupSql(entity.GroupId, out DynamicParameters parameter3));
                parameterList.Add(parameter3);

                foreach (var item in planEntities)
                {
                    item.GroupId = entity.GroupId;
                    sqls.Add(GetInsertGroupPlanSql(item, out DynamicParameters parameter));
                    parameterList.Add(parameter);
                }
            }
            sqls.Add(this.GetDeleteGroupLocationSql(entity.GroupId, out DynamicParameters parameter4));
            parameterList.Add(parameter4);
            foreach (var item in locationEntities)
            {
                item.GroupId = entity.GroupId;
                item.AdminId = entity.AdminId;
                item.AdminName = entity.AdminName;
                sqls.Add(GetInsertGroupLocationSql(item, out DynamicParameters parameter));
                parameterList.Add(parameter);
            }

            sqls.Add(this.GetDeleteGroupWifiSql(entity.GroupId, out DynamicParameters parameter5));
            parameterList.Add(parameter5);
            foreach (var item in wiFiEntities)
            {
                item.GroupId = entity.GroupId;
                item.AdminId = entity.AdminId;
                item.AdminName = entity.AdminName;
                sqls.Add(GetInsertGroupWifiSql(item, out DynamicParameters parameter));
                parameterList.Add(parameter);
            }
            var executeCount = await this.databaseContext.Master.TransactionExecuteAsync(sqls, parameterList, databaseContext.Transaction);
            if (executeCount > 0)
            {
                string querySql = string.Format(@"{0} AND GroupId=" + entity.GroupId, QUERY);
                return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceGroupEntity>(querySql);
            }
            return new AttendanceGroupEntity();
        }

        private string GetDeleteGroupWifiSql(long groupId, out DynamicParameters parameters)
        {
            string sql = "Delete from OA_AttendanceWiFi Where GroupId=?GroupId;";
            parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return sql;
        }

        private string GetDeleteGroupLocationSql(long groupId, out DynamicParameters parameters)
        {
            string sql = "Delete from OA_AttendanceLocation Where GroupId=?GroupId;";
            parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return sql;
        }

        private string GetDeleteTurnGroupSql(long groupId, out DynamicParameters parameters)
        {
            string sql = "Delete from OA_AttendancePlan Where GroupId=?GroupId;";
            parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return sql;
        }

        private string GetDeleteFixedGroupSql(long groupId, out DynamicParameters parameters)
        {
            string sql = "Delete from OA_AttendanceGroupFixed Where GroupId=?GroupId;";
            parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return sql;
        }

        private string GetUpdateGroupSql(AttendanceGroupEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceGroup", new string[] { "ClassId", "GroupName", "IgnoreMemberIds", "MemberCount", "DepartmentIds", "ManagerIds", "IsAllowOuterCheck", "IsOuterRemark", "ModeIds", "IsDefault", "LaterMinutes", "StartTime", "WorkDate", "CycleDays", "PlanName", "PlanClassIds", "IsAutoCheck", "ModifierId", "ModifierName", "ModifyTime" }, new string[] { "GroupId" });
            parameters.Add("?GroupId", entity.GroupId, DbType.Int64);
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64);
            parameters.Add("?GroupName", entity.GroupName, DbType.AnsiString, size: 50);
            parameters.Add("?Type", entity.Type, DbType.AnsiString, size: 50);
            parameters.Add("?IgnoreMemberIds", entity.IgnoreMemberIds, DbType.AnsiString, size: 50);
            parameters.Add("?MemberCount", entity.MemberCount, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int64);
            parameters.Add("?DepartmentIds", entity.DepartmentIds, DbType.AnsiString, size: 50);
            parameters.Add("?ManagerIds", entity.ManagerIds, DbType.AnsiString, size: 50);
            parameters.Add("?IsAllowOuterCheck", entity.IsAllowOuterCheck, DbType.Boolean);
            parameters.Add("?IsOuterRemark", entity.IsOuterRemark, DbType.Boolean);
            parameters.Add("?ModeIds", entity.ModeIds, DbType.AnsiString, size: 50);
            parameters.Add("?IsDefault", entity.IsDefault, DbType.Boolean);
            parameters.Add("?LaterMinutes", entity.LaterMinutes, DbType.Int32);
            parameters.Add("?StartTime", entity.StartTime, DbType.AnsiString, size: 50);
            parameters.Add("?WorkDate", entity.WorkDate, DbType.AnsiString, size: 50);
            parameters.Add("?PlanClassIds", entity.PlanClassIds, DbType.AnsiString, size: 50);
            parameters.Add("?PlanName", entity.PlanName, DbType.AnsiString, size: 50);
            parameters.Add("?CycleDays", entity.CycleDays, DbType.Int32);
            parameters.Add("?IsAutoCheck", entity.IsAutoCheck, DbType.Boolean);
            parameters.Add("?ModifierId", entity.ModifierId, DbType.Int64);
            parameters.Add("?ModifierName", entity.ModifierName, DbType.AnsiString, size: 50);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            return sql;
        }

        /// <summary>
        /// 根据员工部门查询员工所属考勤组
        /// </summary>
        /// <param name="departmentId">部门Id</param>
        /// <param name="companyId">公司Id</param>
        /// <returns></returns>
        public async Task<AttendanceGroupEntity> GetEntityByDepartmentIdAndCompanyIdAsync(int departmentId, int companyId)
        {
            string sql = string.Format("{0} AND  FIND_IN_SET(?DepartmentId,DepartmentIds) AND CompanyId=?CompanyId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceGroupEntity>(sql, parameters);
        }

        /// <summary>
        /// 根据员工部门查询员工所属考勤组Id
        /// </summary>
        /// <param name="departmentId">部门Id</param>
        /// <param name="companyId">公司Id</param>
        /// <returns></returns>
        public async Task<long> GetGroupIdByDepartmentIdAndCompanyIdAsync(int departmentId, int companyId)
        {
            string sql = "SELECT GroupId FROM  OA_AttendanceGroup Where  Status<>'Abandon' AND  FIND_IN_SET(?DepartmentId,DepartmentIds) AND CompanyId=?CompanyId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            return await this.databaseContext.Slave.ExecuteScalarAsync<long>(sql, parameters);
        }

        /// <summary>
        /// 根据考勤组Id获取排班信息列表
        /// </summary>
        /// <param name="groupId">考勤组Id</param>
        /// <returns></returns>
        public async Task<IEnumerable<AttendancePlanViewEntity>> GettGroupPlanByGroupId(long groupId)
        {

            string sql = "SELECT A.*,B.WeekDay as ClassWeekDay,B.RestTime,B.ClassName FROM  OA_AttendancePlan A  Left JOIN OA_AttendanceClass B ON A.ClassId = B.ClassId   WHERE 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<AttendancePlanViewEntity>(string.Format("{0} AND GroupId=?GroupId", sql), parameters);
        }

        /// <summary>
        /// 更新指定的考勤组有效日期
        /// </summary>
        /// <param name="groupId">考勤组Id</param>
        /// <param name="dateTime">日期</param>
        /// <returns></returns>
        public async Task<AttendanceGroupEntity> UpdateEffectiveDateAsync(long groupId, DateTime dateTime)
        {
            string sql =SqlCommands.GetUpdateSql("OA_AttendanceGroup", new string[] { "EffectiveDate", "ModifyTime" }, new string[] { "GroupId" });
            // string deleteDatesSql = "Delete from  OA_AttendanceClassDate Where GroupId=?GroupId AND Year=?Year AND FIND_IN_SET(Month,?Month);";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GroupId", groupId, DbType.Int64);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?EffectiveDate", dateTime, DbType.DateTime);
            //parameters.Add("?Year", DateTime.Now.Year, DbType.Int32);
            //parameters.Add("?Month", string.Join(",", new long[] { DateTime.Now.Month, DateTime.Now.AddMonths(1).Month }), DbType.AnsiString);
            string querySql = string.Format("{0} {1} AND GroupId=?GroupId;", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceGroupEntity>(querySql, parameters, databaseContext.Transaction);
        }
        #endregion

    }
}
